﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SunshineAirlines
{
    public partial class TicketSalesDDetail : Form
    {
        public TicketSalesDDetail()
        {
            InitializeComponent();
        }
        string ScheduleId="";
        public TicketSalesDDetail(string ScheduleId)
        {
            this.ScheduleId = ScheduleId;
            InitializeComponent();
        }
        private void TicketSalesDDetail_Load(object sender, EventArgs e)
        {
            string sql = "select CONCAT( DepartureAirportIATA+'to'+ArrivalAirportIATA,',',Date,',',Time,',',Name) as 'Flight' from Schedule,Aircraft,Route where Aircraft.AircraftId = Schedule.AircraftId and Route.RouteId = Schedule.RouteId and ScheduleId = '"+ScheduleId+"'";
            label1.Text = DBHelper.ExecuteScalar(sql).ToString();
            string sql1 = "select Aircraft.AircraftId from Aircraft,Schedule where Aircraft.AircraftId=Schedule.AircraftId and ScheduleId = '" + ScheduleId + "'";
            if ((int)DBHelper.ExecuteScalar(sql1) == 1)
            {
                pictureBox1.Load("Aircraft-Boeing 737-800.jpg");
                string sql2 = "select  count(ReservationId) from FlightReservation where ScheduleId='"+ScheduleId+"' and CabinTypeId='3'";
                string sql3 = "select  count(ReservationId) from FlightReservation where ScheduleId='" + ScheduleId + "' and CabinTypeId='2'";
                string sql4 = "select  count(ReservationId) from FlightReservation where ScheduleId='" + ScheduleId + "' and CabinTypeId='1'";
                string sql5 = "select count(Id)  from FlightReservation,SeatLayout where ScheduleId='" + ScheduleId + "' and Aircraftid=1 and FlightReservation.SeatLayoutId=SeatLayout.Id and SeatLayout.CabinTypeId='3'";
                string sql6 = "select count(Id)  from FlightReservation,SeatLayout where ScheduleId='" + ScheduleId + "' and Aircraftid=1 and FlightReservation.SeatLayoutId=SeatLayout.Id and SeatLayout.CabinTypeId='2'";
                string sql7 = "select count(Id)  from FlightReservation,SeatLayout where ScheduleId='" + ScheduleId + "' and Aircraftid=1 and FlightReservation.SeatLayoutId=SeatLayout.Id and SeatLayout.CabinTypeId='1'";
                double a = float.Parse(DBHelper.ExecuteScalar(sql2).ToString())*100/ 8;
                double b = float.Parse(DBHelper.ExecuteScalar(sql3).ToString()) *100/ 60;
                double c = float.Parse(DBHelper.ExecuteScalar(sql4).ToString()) *100/ 120;
                label5.Text = ""+DBHelper.ExecuteScalar(sql2).ToString()+"/8 "+ a.ToString("#0.00")+ "%";
                label6.Text = "" + DBHelper.ExecuteScalar(sql3).ToString() + "/60 " + b.ToString("#0.00") + "%";
                label7.Text = "" + DBHelper.ExecuteScalar(sql4).ToString() + "/120 " + c.ToString("#0.00") + "%";
                label8.Text = "Total Tickets:8";
                label9.Text = "Sold Tickets:"+DBHelper.ExecuteScalar(sql2).ToString()+"";
                label10.Text = "Selected Seats:"+DBHelper.ExecuteScalar(sql5).ToString()+"";
                label11.Text = "Total Tickets:60";
                label12.Text = "Sold Tickets:" + DBHelper.ExecuteScalar(sql3).ToString() + "";
                label13.Text = "Selected Seats:" + DBHelper.ExecuteScalar(sql6).ToString() + "";
                label14.Text = "Total Tickets:120";
                label15.Text = "Sold Tickets:" + DBHelper.ExecuteScalar(sql4).ToString() + "";
                label16.Text = "Selected Seats:" + DBHelper.ExecuteScalar(sql7).ToString() + "";
            }
            if ((int)DBHelper.ExecuteScalar(sql1) == 2)
            {
                pictureBox1.Load("Aircraft-Airbus  319.jpg");
                string sql2 = "select  count(ReservationId) from FlightReservation where ScheduleId='" + ScheduleId + "' and CabinTypeId='3'";
                string sql3 = "select  count(ReservationId) from FlightReservation where ScheduleId='" + ScheduleId + "' and CabinTypeId='2'";
                string sql4 = "select  count(ReservationId) from FlightReservation where ScheduleId='" + ScheduleId + "' and CabinTypeId='1'";
                string sql5 = "select count(Id)  from FlightReservation,SeatLayout where ScheduleId='" + ScheduleId + "' and aircraftid=1 and FlightReservation.SeatLayoutId=SeatLayout.Id and SeatLayout.CabinTypeId='3'";
                string sql6 = "select count(Id)  from FlightReservation,SeatLayout where ScheduleId='" + ScheduleId + "' and aircraftid=1 and FlightReservation.SeatLayoutId=SeatLayout.Id and SeatLayout.CabinTypeId='2'";
                string sql7 = "select count(Id)  from FlightReservation,SeatLayout where ScheduleId='" + ScheduleId + "' and aircraftid=1 and FlightReservation.SeatLayoutId=SeatLayout.Id and SeatLayout.CabinTypeId='1'";
                double a = float.Parse(DBHelper.ExecuteScalar(sql2).ToString()) * 100 / 12;
                double b = float.Parse(DBHelper.ExecuteScalar(sql3).ToString()) * 100 / 60;
                double c = float.Parse(DBHelper.ExecuteScalar(sql4).ToString()) * 100 / 90;
                label5.Text = "" + DBHelper.ExecuteScalar(sql2).ToString() + "/12 " + a.ToString("#0.00") + "%";
                label6.Text = "" + DBHelper.ExecuteScalar(sql3).ToString() + "/60 " + b.ToString("#0.00") + "%";
                label7.Text = "" + DBHelper.ExecuteScalar(sql4).ToString() + "/90 " + c.ToString("#0.00") + "%";
                label8.Text = "Total Tickets:12";
                label9.Text = "Sold Tickets:" + DBHelper.ExecuteScalar(sql2).ToString() + "";
                label10.Text = "Selected Seats:" + DBHelper.ExecuteScalar(sql5).ToString() + "";
                label11.Text = "Total Tickets:60";
                label12.Text = "Sold Tickets:" + DBHelper.ExecuteScalar(sql3).ToString() + "";
                label13.Text = "Selected Seats:" + DBHelper.ExecuteScalar(sql6).ToString() + "";
                label14.Text = "Total Tickets:90";
                label15.Text = "Sold Tickets:" + DBHelper.ExecuteScalar(sql4).ToString() + "";
                label16.Text = "Selected Seats:" + DBHelper.ExecuteScalar(sql7).ToString() + "";
            }
            string sql9 = "select concat(ColumnName,RowNumber) as 'a' from SeatLayout,FlightReservation where FlightReservation.ScheduleId=SeatLayout.Id and ScheduleId='" + ScheduleId + "' ";
            //button
            string[] num = {"L","K","J","C","B","A" };
            if ((int)DBHelper.ExecuteScalar(sql1) == 1)
            {
                int n = 3;
                for (int i = 3; i <=32; i++)
                {

                    for (int a = 0; a < 6; a++)
                    {
                        Button btn = new Button();
                        btn.Size = new System.Drawing.Size(35, 20);
                        btn.Text = "" + num[a] + "" + n + "";
                        btn.Location = new Point(240 + 35 * i, 350 + a * 20);
                        this.Controls.Add(btn);
                        if (btn.Text == Convert.ToString(DBHelper.ExecuteScalar(sql9)))
                        {
                            btn.BackColor = Color.Orange;
                        }
                    }
                    n++;
                }
            }
            if ((int)DBHelper.ExecuteScalar(sql1) == 2)
            {
                int n = 4;
                for (int i = 4; i <=28; i++)
                {

                    for (int a = 0; a < 6; a++)
                    {
                        Button btn = new Button();
                        btn.Size = new System.Drawing.Size(35, 20);
                        btn.Text = "" + num[a] + "" + n + "";
                        btn.Location = new Point(240 + 35 * i, 350 + a * 20);
                        this.Controls.Add(btn);
                        if (btn.Text == Convert.ToString(DBHelper.ExecuteScalar(sql9)))
                        {
                            btn.BackColor = Color.Orange;
                        }
                    }
                    n++;
                }
            }
        }
    }
    }
